﻿Imports System.Data.OleDb

Public Class QuizManagement
    Inherits ConnectionClass

    Public Sub InsertQuiz(ByVal userID As Int32, ByVal quizName As String, ByVal QuizDescription As String, ByVal QuizDate As DateTime, ByVal QuizPicture As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "INSERT INTO Quizes(UserID,QuizName,QuizDescription,QuizDate,QuizPicture) VALUES(@f1,@f2,@f3,@f4,@f5)"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", userID)
            newCmd.Parameters.AddWithValue("@f2", quizName)
            newCmd.Parameters.AddWithValue("@f3", QuizDescription)
            newCmd.Parameters.AddWithValue("@f4", QuizDate)
            newCmd.Parameters.AddWithValue("@f5", QuizPicture)
            newCmd.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub


    Public Function SelectTopQuizID() As Int32
        Try

            myConnection.Open()
            Dim Cmd As OleDbCommand = New OleDbCommand("Select Top 1 QuizId from Quizes Order By QuizId Desc", myConnection)
            Dim dr = Cmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)
            myConnection.Close()
            Return lastId

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizByUserId(ByVal userID As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "Select * From Quizes Where UserID=@f1"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", userID)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizzesLatest() As OleDbDataReader
        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT  QuizName, QuizDescription, QuizDate, QuizPicture, QuizId, UserId FROM(Quizes) ORDER BY QuizId DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text

            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizzesLatestByTag(ByVal tagId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  Quizes.QuizName, Quizes.QuizDescription, Quizes.QuizDate, Quizes.QuizPicture, Quizes.QuizId, Quizes.UserId FROM (Quizes INNER JOIN Quiz_Tags ON Quizes.QuizId = Quiz_Tags.QuizId) WHERE (Quiz_Tags.TagId = @TagId) ORDER BY Quizes.QuizId DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", tagId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizzesLatestByTeacherId(ByVal teacherId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT  QuizName, QuizDescription, QuizDate, QuizPicture, QuizId, UserId FROM(Quizes) where UserId = @f1 ORDER BY QuizId DESC"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", teacherId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizByQuizId(ByVal quizId As Int32) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "Select * From Quizes Where QuizId=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", quizId)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectQuizTeacherEmailByQuizId(ByVal quizId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT aspnet_Membership.Email FROM (aspnet_Membership INNER JOIN Quizes ON aspnet_Membership.UserId = Quizes.UserId) WHERE (Quizes.QuizId = @f1)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", quizId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Sub UpdateQuizByQuizIDWithPicture(ByVal quizID As Int32, ByVal QuizName As String, ByVal QuizDescription As String, ByVal QuizDate As DateTime, ByVal QuizPicture As String)
        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE   Quizes Set QuizName =@f1, QuizDescription =@f2, QuizDate =@f3, QuizPicture =@f4 where QuizID = @f5"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", QuizName)
            newCmd.Parameters.AddWithValue("@f2", QuizDescription)
            newCmd.Parameters.AddWithValue("@f3", QuizDate)
            newCmd.Parameters.AddWithValue("@f4", QuizPicture)
            newCmd.Parameters.AddWithValue("@f5", quizID)

            newCmd.ExecuteNonQuery()
            myConnection.Close()

        Catch ex As Exception

        End Try
    End Sub

    Public Sub UpdateQuizByQuizIDWithoutPicture(ByVal quizID As Int32, ByVal QuizName As String, ByVal QuizDescription As String, ByVal QuizDate As DateTime)
        Try


            myConnection.Open()
            Dim SqlString As String = "UPDATE Quizes Set QuizName =@f1, QuizDescription =@f2, QuizDate =@f3  where QuizID = @f5"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", QuizName)
            newCmd.Parameters.AddWithValue("@f2", QuizDescription)
            newCmd.Parameters.AddWithValue("@f3", QuizDate)
            newCmd.Parameters.AddWithValue("@f5", quizID)

            newCmd.ExecuteNonQuery()
            myConnection.Close()

        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectQuizSearchByQuizContentDate(ByVal QuizContent As String, ByVal QuizDate As DateTime) As OleDbDataReader
        Try

            myConnection.Open()
            Dim SqlString As String = "SELECT QuizId, QuizName, QuizDescription, QuizPicture, QuizDate FROM Quizes Where ((QuizName Like '%' +@f1 + '%' or QuizDescription Like '%' + @f1 + '%') and QuizDate <= @f2)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", QuizContent)
            cmd.Parameters.AddWithValue("@f2", QuizDate)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            Return dr
            myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    '

    Public Function SelectQuizSearchByQuizContentDateByTeacher(ByVal QuizContent As String, ByVal QuizDate As DateTime, ByVal userId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  QuizId, QuizName, QuizDescription, QuizPicture, QuizDate FROM  Quizes WHERE ((QuizName LIKE '%' + @f1 + '%') AND (QuizDate <= @f2) OR (QuizDate <= @f2) AND (QuizDescription LIKE '%' + @f1 + '%')) AND (UserId = @f3)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", QuizContent)
        cmd.Parameters.AddWithValue("@f2", QuizDate)
        cmd.Parameters.AddWithValue("@f3", userId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function SelectLatestQuizesForWebService() As DataSetTables.QuizesDataTable
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT  QuizName, QuizDescription, QuizDate, QuizPicture, QuizId, UserId FROM(Quizes) ORDER BY QuizId DESC"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        Dim dr As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Dim data As DataSetTables.QuizesDataTable = New DataSetTables.QuizesDataTable()
        dr.Fill(data)
        Return data
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


End Class
